use std::convert::TryInto;
use chrono::NaiveDateTime;
use serde_derive::{Deserialize, Serialize};
use sqlx::{MySqlPool, mysql::MySqlRow, Row};
use crate::extends::core::pager::page_helper::PageHelper;
use crate::extends::core::response::result_data::ResultData;
use crate::extends::core::db::data_converter::DataConverter;
use crate::extends::core::db::select_builder::SelectBuilder;
use crate::extends::core::db::sql_dialect::DatabaseDialect;
use crate::extends::core::db::delete_builder::DeleteBuilder;
use crate::extends::core::db::insert_builder::InsertBuilder;
use crate::extends::core::db::update_builder::UpdateBuilder;
use crate::extends::core::db::sql_direction::DirectionEnum;
use crate::extends::core::pager::order::Order;
use crate::extends::core::pager::pager::Pager;
use crate::extends::core::pager::sorter::Sorter;
use crate::extends::core::deserializer::date_format_deserializer::DateFormatDeserializer;

/// 租户结构体
#[derive(Debug, Clone, Deserialize, Serialize)]
pub struct Tenant {
    pub id: Option<i32>,                    // 主键ID
    pub tenant_code: Option<String>,        // 租户编码
    pub tenant_name: Option<String>,        // 租户名称
    pub api_key: Option<String>,            // 租户ApiKey
    pub num_tokens: Option<i32>,            // api调用计算token总数
    pub create_by: Option<String>,          // 创建者
    #[serde(default, with = "DateFormatDeserializer")]
    pub created_at: Option<NaiveDateTime>,  // 创建时间
    pub update_by: Option<String>,          // 更新者
    #[serde(default, with = "DateFormatDeserializer")]
    pub updated_at: Option<NaiveDateTime>,  // 更新时间
    pub remark: Option<String>,             // 备注
    pub status: Option<i32>,                // 状态:1=正常,2=停用
}

impl Tenant {
    /// 创建一个新的 `Tenant` 实例，并初始化所有字段为 `None`
    pub fn new() ->Tenant{
        Tenant{
            id: None,
            tenant_code: None,
            tenant_name: None,
            api_key: None,
            num_tokens: None,
            create_by: None,
            created_at: None,
            update_by: None,
            updated_at: None,
            remark: None,
            status: None,
        }
    }
    // 获取模型对应的表名
    pub fn table_name() -> &'static str {
        "t_tenant"
    }

    /// 从数据库行创建租户实例
    fn from_row(row: &MySqlRow) -> Tenant {
        Tenant {
            id: row.try_get("id").ok(),
            tenant_code: row.try_get("tenant_code").ok(),
            tenant_name: row.try_get("tenant_name").ok(),
            api_key: row.try_get("api_key").ok(),
            num_tokens: row.try_get("num_tokens").ok(),
            create_by: row.try_get("create_by").ok(),
            created_at: row.try_get("created_at").ok(),
            update_by: row.try_get("update_by").ok(),
            updated_at: row.try_get("updated_at").ok(),
            remark: row.try_get("remark").ok(),
            status: row.try_get("status").ok(),
        }
    }

    /// 保存新租户到数据库
    ///
    /// # 参数
    /// * `tenant` - 租户实例
    /// * `pool` - MySQL数据库连接池
    ///
    /// # 返回值
    /// 返回新插入的租户ID
    pub async fn save(tenant: &Tenant, pool: &MySqlPool) -> Option<i32> {
        let sql = InsertBuilder::build(Tenant::table_name(), tenant);
        match sqlx::query(&sql).execute(pool).await {
            Ok(v) => {
                if v.rows_affected() > 0 {
                    v.last_insert_id().try_into().ok()
                } else {
                    None
                }
            }
            Err(e) => {
                log::error!("Failed to insert tenant: {:?}", e);
                None
            }
        }
    }

    /// 更新数据库中的租户信息
    ///
    /// # 参数
    /// * `tenant` - 租户实例
    /// * `pool` - MySQL数据库连接池
    ///
    /// # 返回值
    /// 返回操作是否成功
    pub async fn update(tenant: &Tenant, pool: &MySqlPool) -> Option<bool> {
        let sql = UpdateBuilder::build(Tenant::table_name(), tenant, &format!("id = '{}'", tenant.id.unwrap()));
        match sqlx::query(&sql).execute(pool).await {
            Ok(v) => Some(v.rows_affected() > 0),
            Err(e) => {
                log::error!("Failed to update tenant: {:?}", e);
                None
            }
        }
    }

    /// 删除数据库中的租户
    ///
    /// # 参数
    /// * `id` - 租户ID
    /// * `pool` - MySQL数据库连接池
    ///
    /// # 返回值
    /// 返回操作是否成功
    pub async fn delete(id: &i32, pool: &MySqlPool) -> Option<bool> {
        let sql = DeleteBuilder::build(Tenant::table_name(), "id", &id.to_string());
        match sqlx::query(&sql).execute(pool).await {
            Ok(v) => Some(v.rows_affected() > 0),
            Err(e) => {
                log::error!("Failed to delete tenant: {:?}", e);
                None
            }
        }
    }

    /// 根据租户ID查询租户信息
    ///
    /// # 参数
    /// * `id` - 租户ID
    /// * `pool` - MySQL数据库连接池
    ///
    /// # 返回值
    /// 返回查询到的租户实例
    pub async fn find_by_id(id: &i32, pool: &MySqlPool) -> Option<Tenant> {
        let mut builder_mysql = SelectBuilder::new(DatabaseDialect::MySQL);
        let fields = DataConverter::get_field_names_to_string(&Tenant::new());
        let query_sql = builder_mysql
            .select(&fields)
            .from_table(Tenant::table_name())
            .where_clause("id = :id", "id", &serde_json::json!(id))
            .build();
        match sqlx::query(&query_sql).fetch_one(pool).await {
            Ok(row) => Some(Tenant::from_row(&row)),
            Err(e) => {
                log::error!("Failed to find tenant by id: {:?}", e);
                None
            }
        }
    }

    /// 根据条件查询租户数量
    ///
    /// # 参数
    /// * `tenant` - 包含查询条件的租户实例
    /// * `pool` - MySQL数据库连接池
    ///
    /// # 返回值
    /// 返回符合条件的租户数量
    pub async fn find_by_count(tenant: &Tenant, pool: &MySqlPool) -> Option<i64> {
        let mut builder_mysql = SelectBuilder::new(DatabaseDialect::MySQL);
        let search_params = DataConverter::convert_to_map(tenant, true);

        builder_mysql
            .select("count(1)")
            .from_table(Tenant::table_name());

        // 动态生成WHERE子句
        for (key, value) in &search_params {
            builder_mysql.where_clause(&format!("{} = :{}", key, key), key, value);
        }

        let query_sql = builder_mysql.build_count();

        match sqlx::query(&query_sql).fetch_one(pool).await {
            Ok(row) => Some(row.get::<i64, _>("count")),
            Err(e) => {
                log::error!("Failed to count tenants: {:?}", e);
                None
            }
        }
    }

    /// 根据条件分页查询租户
    ///
    /// # 参数
    /// * `search` - 包含分页和查询条件的PageHelper实例
    /// * `pool` - MySQL数据库连接池
    ///
    /// # 返回值
    /// 返回符合条件的租户列表和分页信息
    pub async fn find(
        search: &PageHelper<Tenant>,
        pool: &MySqlPool,
    ) -> ResultData<Tenant> {
        let mut builder_mysql = SelectBuilder::new(DatabaseDialect::MySQL);
        let search_params = DataConverter::convert_to_map(&search.query, true);
        let fields = DataConverter::get_field_names_to_string(&search.query);
        // 计算偏移量
        let offset = (search.pager.page_num - 1) * search.pager.page_size;
        // 构建查询以获取分页结果和总计数
        builder_mysql
            .select(&format!("{}, COUNT(*) OVER() as total_count",&fields))
            .from_table(Tenant::table_name());
        // 动态生成WHERE子句
        for (key, value) in &search_params {
            builder_mysql.where_clause(&format!("{}=:{}", key, key), key, value);
        }

        let query_sql = builder_mysql.order_by("created_at", "DESC")
            .limit(offset, Some(search.pager.page_size))
            .build();

        // 获取带有总计数的分页结果
        match sqlx::query(&query_sql).fetch_all(pool).await {
            Ok(rows) => {
                let total_count = if !rows.is_empty() {
                    rows[0].get::<i64, _>("total_count")
                } else {
                    0
                };
                let total_pages = (total_count as f64 / search.pager.page_size as f64).ceil() as usize;
                let pager = Pager::new(
                    search.pager.page_num,
                    search.pager.page_size,
                    Some(total_pages),
                    Some(total_count as usize),
                );
                let sorter = Sorter::new(vec![
                    Order {
                        property: "created_at".to_string(),
                        direction: DirectionEnum::DESC,
                    },
                ]);
                let list = rows.iter().map(Tenant::from_row).collect();
                ResultData::builder(Some(list), Some(pager), Some(sorter))
            }
            Err(e) => {
                log::error!("Failed to find tenants by example: {:?}", e);
                ResultData::none()
            }
        }
    }
}
